Using Scrollable Cursors

Scrollable cursors can move backward and forward in a result set, allowing the application to scroll back and forth through retrieved data.

Result Set Types

JDBC defines the following result set types:

Forward-only
Scroll-insensitive
Scroll-sensitive

Forward-only result sets allow you to move forward, but not backward, through the data. The application can move only forward using the next() method.

Typically, a scroll-insensitive result set ignores changes that are made while it is open. It provides a static view of the underlying data it contains. The membership, order, and column values of rows are fixed when the result set is created.

In contrast, a scroll-sensitive result set provides a dynamic view of the underlying data, reflecting changes that are made while it is open. The membership and ordering of rows in the result set may be fixed, depending on how it is implemented.

Support for the scroll-sensitive cursors depends on the data store to which you are connecting, as described in Table 8-3.

Table 8-3. Support for Scroll-Sensitive Cursors (JDBC)

Database	Conditions for Support
DB2 UDB	The DB2 tables must contain an auto-unique column. The name and other properties of the auto-unique column must be configured in the data source of the SequeLink Server.
Informix	None (inherently supported).
Microsoft SQL Server	The table must contain an identity column.
JDBC Socket	The backend database must support an auto-unique column. The name and other properties of the auto-unique column must be configured in the data source of the SequeLink Server.
ODBC Socket	The backend database must support an auto-unique column. The name and other properties of the auto-unique column must be configured in the data source of the SequeLink Server.
Oracle	None (inherently supported).
Sybase	The table must contain an identity column.

Concurrency Types

JDBC defines the following concurrency types for a result set:

Read-only
Updatable

A read-only result set does not allow its contents to be updated. Read-only result sets can increase the overall level of concurrency between transactions, because multiple read-only locks can be held on a data item simultaneously.

An updatable result set allows its contents to be updated and may use database write locks to mediate access to the same data item by different transactions. Because only a single write lock may be held at one time on a data item, updatable result sets can reduce concurrency.

An optimistic concurrency control scheme may be appropriate if you can accurately predict that conflicting access to data will seldom occur. Typically, optimistic concurrency control implementations compare rows by a value or by a version number to determine if an update conflict has occurred.

Using Scrollable Cursors

The JDBC driver supports forward-only and scroll-insensitive result sets against all data stores.
Scroll-sensitive result sets on stored procedures or explicit batches are not supported.
Scroll-sensitive result sets are not supported when the Select statement contains any of the following SQL language constructions:
JOIN
Aggregate functions
GROUP BY
The JDBC driver supports updatable result sets.

NOTE: When the JDBC driver cannot support the requested result set type or concurrency, it will automatically downgrade it and generate one or multiple SQLWarnings with detailed information.